package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.operations.dataentry.OperationCodes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.ArrayList;

/**
 * Created by Sudhakar.
 * User: Administrator
 * Date: Sep 30, 2005
 * Time: 5:00:20 PM

 */
public class OperationCodeDAO
{

    /**
     *
     * @return
     * @throws com.scalar.ScalarException
     */
    public static String checkOperationCode( String operationCode ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String process          = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT PROCESS_TYPE FROM OPERATION_CODE_TABLE WHERE OPERATION_CODE = ? " );
            pstmt.setString( 1, operationCode );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                process = rset.getString( "PROCESS_TYPE" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load process from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return process;
    }
    /**
     *
     * @param operationCode
     * @param operationDesc
     * @param process
     * @throws ScalarException
     */
    public static void saveOperationCode( String operationCode, String operationDesc, String process ) throws ScalarException
    {
        DBTransaction dbTransaction = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString( todayDate, "MM/dd/yyyy" );

            pstmt = conn.prepareStatement( "Insert into Operation_Code_Table (Operation_Code, Operation_Code_Full_Desc, Process_Type, Timestamp ) values ( ?, ?, ?, ? )" );

            if ( operationCode != null && operationCode.trim().length() > 0 && operationDesc != null && operationDesc.trim().length() > 0 )
            {
                pstmt.setString( 1, operationCode );
                pstmt.setString( 2, operationDesc );
                pstmt.setString( 3, process );
                pstmt.setString( 4, currentDate );

                pstmt.executeUpdate();
            }

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Shipping Lot Information into database", sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    /**
     *
     * @param operationCode
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadOperationCodes( String operationCode ) throws ScalarException
    {
        Connection conn                 = null;
        PreparedStatement pstmt         = null;
        ResultSet rset                  = null;
        OperationCodes operationCodes   = null;
        ArrayList oprCodesList          = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            if ( operationCode != null )
            {
                pstmt = conn.prepareStatement( "SELECT OPERATION_CODE, OPERATION_CODE_FULL_DESC, PROCESS_TYPE FROM OPERATION_CODE_TABLE WHERE OPERATION_CODE = ? " );
                pstmt.setString( 1, operationCode );
                rset = pstmt.executeQuery();
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT OPERATION_CODE, OPERATION_CODE_FULL_DESC, PROCESS_TYPE, CASE  WHEN PROCESS_TYPE = 'ASSEMBLY' THEN 1 WHEN PROCESS_TYPE = 'TEST' THEN 2 END AS PROCESS_TYPE_ID FROM OPERATION_CODE_TABLE ORDER BY PROCESS_TYPE_ID, CAST( OPERATION_CODE AS INT ) " );
                rset = pstmt.executeQuery();
            }

            while ( rset.next() )
            {
                operationCodes = new OperationCodes();
                operationCodes.setOperationCode( rset.getString( "OPERATION_CODE" ) );
                operationCodes.setOprationDesc( rset.getString( "OPERATION_CODE_FULL_DESC" ) );
                operationCodes.setProcessType( rset.getString( "PROCESS_TYPE" ) );

                oprCodesList.add( operationCodes );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load process from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return oprCodesList;
    }
    /**
     *
     * @param operationCode
     * @param operationDesc
     * @param process
     * @throws ScalarException
     */
    public static void modifyOperationCode( String operationCode, String operationDesc, String process ) throws ScalarException
    {
        DBTransaction dbTransaction = null;
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        boolean success             = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString( todayDate, "MM/dd/yyyy" );

            pstmt = conn.prepareStatement( "UPDATE OPERATION_CODE_TABLE SET OPERATION_CODE_FULL_DESC = ?, PROCESS_TYPE = ?, TIMESTAMP = ? WHERE OPERATION_CODE=?" );

            if ( operationCode != null && operationCode.trim().length() > 0 && operationDesc != null && operationDesc.trim().length() > 0 )
            {
                pstmt.setString( 1, operationDesc );
                pstmt.setString( 2, process );
                pstmt.setString( 3, currentDate );
                pstmt.setString( 4, operationCode );

                pstmt.executeUpdate();
            }

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Modify operation code Information into database", sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
    /**
     *
     * @param operationCode
     * @throws ScalarException
     */
    public static void deleteOperationCode( String operationCode ) throws ScalarException
    {
        DBTransaction dbTransaction = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "DELETE FROM OPERATION_CODE_TABLE WHERE OPERATION_CODE=?" );

            if ( operationCode != null && operationCode.trim().length() > 0  )
            {
                pstmt.setString( 1, operationCode );
                pstmt.executeUpdate();
            }

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Modify operation code Information into database", sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore)
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
    }
}
